package com.chen.dao;

import com.chen.entity.FlightEntity;
import com.chen.utils.ChenJdbcUtils;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/*
 *    在软件开发中，DAO 是 Data Access Object 的缩写，中文意思是数据访问对象。
 * 它是一种设计模式，用于抽象和封装数据访问层的实现细节。DAO 模式提供了一个接口，
 * 用于访问和操作底层的数据源，如数据库、文件系统等，而不需要暴露数据源的具体实现细节。
 * */
public class FlightDao {

    /*
     * 查询所有数据
     * */
    public List<FlightEntity> getByAll() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1.获取连接
            connection = ChenJdbcUtils.getConnection();
            //2.获取执行者对象
            preparedStatement = connection.prepareStatement("select * from chen_flight where is_delete = 0");
            resultSet = preparedStatement.executeQuery();
            ArrayList<FlightEntity> flightEntities = new ArrayList<>();
            while (resultSet.next()) {
                Integer id = resultSet.getInt("id");
                String flightId = resultSet.getString("flight_id");
                String company = resultSet.getString("company");
                String departureAirport = resultSet.getString("departure_airport");
                String arriveAirport = resultSet.getString("arrive_airport");
                Date departureTime = resultSet.getDate("departure_time");
                Date arriveTime = resultSet.getDate("arrive_time");
                String model = resultSet.getString("model");
                Float cost = resultSet.getFloat("cost");
                Integer isDelete = resultSet.getInt("is_delete");
                FlightEntity flightEntity = new FlightEntity(
                        id, flightId, company, departureAirport,
                        arriveAirport, departureTime,
                        arriveTime, model, cost, isDelete);
                // 存入到集合中
                flightEntities.add(flightEntity);
            }
            return flightEntities;
        } catch (Exception e) {
            e.printStackTrace();
            ChenJdbcUtils.rollBackTransaction(connection);
            return null;
        } finally {
            // 释放资源
            ChenJdbcUtils.closeConnection(resultSet, preparedStatement, connection);
        }
    }

    /*
     * 根据主键id删除航班信息
     * updata/insert/delete 都需要加上事务
     * */
    public int deleteFlightById(Integer id){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 1.获取连接
            connection = ChenJdbcUtils.getConnection();
            // 2.开启事务
            ChenJdbcUtils.beginTransaction(connection);
            //3.获取执行者对象
            preparedStatement = connection.prepareStatement("delete from chen_flight where id=?");
            preparedStatement.setInt(1, id);
            int result = preparedStatement.executeUpdate();
            ChenJdbcUtils.commitTransaction(connection);
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            ChenJdbcUtils.rollBackTransaction(connection);
            return 0;
        } finally {
            // 释放资源
            ChenJdbcUtils.closeConnection(preparedStatement, connection);
        }
    }

    /*
    * 逻辑删除：
    * 企业一般采用逻辑删除，方便找回数据，如某些用户注销账号之后想撤销注销
    * */
    public int updateDeleteById(Integer id){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        //resultSet
        try {
            // 1.获取连接
            connection = ChenJdbcUtils.getConnection();
            // 2.开启事务
            ChenJdbcUtils.beginTransaction(connection);
            //3.获取执行者对象
            preparedStatement = connection.prepareStatement(
                    "UPDATE `chen`.`chen_flight` SET `is_delete` = 1 WHERE `id` = ?");
            preparedStatement.setInt(1, id);
            int result = preparedStatement.executeUpdate();
            ChenJdbcUtils.commitTransaction(connection);
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            ChenJdbcUtils.rollBackTransaction(connection);
            return 0;
        } finally {
            // 释放资源
            ChenJdbcUtils.closeConnection(preparedStatement, connection);
        }
    }

    /*
     * 根据id查询航班信息
     * */
    public FlightEntity getByIdFlight(Integer id){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1.获取连接
            connection = ChenJdbcUtils.getConnection();
            //2.获取执行者对象
            //preparedStatement = connection.prepareStatement("select * from chen_flight where id=?");
            preparedStatement = connection.prepareStatement(
                    "select * from chen_flight where id=? AND `is_delete` = 0");
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if(!resultSet.next()){
                return null;
            }
            Integer dbId = resultSet.getInt("id");
            String flightId = resultSet.getString("flight_id");
            String company = resultSet.getString("company");
            String departureAirport = resultSet.getString("departure_airport");
            String arriveAirport = resultSet.getString("arrive_airport");
            Date departureTime = resultSet.getDate("departure_time");
            Date arriveTime = resultSet.getDate("arrive_time");
            String model = resultSet.getString("model");
            Float cost = resultSet.getFloat("cost");
            Integer isDelete = resultSet.getInt("is_delete");
            FlightEntity flightEntity = new FlightEntity(
                    dbId, flightId, company, departureAirport,
                    arriveAirport, departureTime, arriveTime,
                    model, cost, isDelete);
            return flightEntity;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            // 释放资源
            ChenJdbcUtils.closeConnection(resultSet, preparedStatement, connection);
        }
    }

    /*
     * 修改航班信息
     * */
    public int updateFlight(FlightEntity flightEntity){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 1.获取连接
            connection = ChenJdbcUtils.getConnection();
            // 2.开启事务
            ChenJdbcUtils.beginTransaction(connection);
            //3.获取执行者对象
            preparedStatement = connection.prepareStatement(
                    "UPDATE `chen`.`chen_flight` SET flight_id = ?," +
                            " `company` = ?, `departure_airport` = ?," +
                            " `arrive_airport` = ?, `departure_time` = ?," +
                            " `arrive_time` = ?, `model` = ?, `cost` = ?, `is_delete` = 0 " +
                            "WHERE `id` = ?");
            preparedStatement.setString(1, flightEntity.getFlightId());
            preparedStatement.setString(2, flightEntity.getCompany());
            preparedStatement.setString(3, flightEntity.getDepartureAirport());
            preparedStatement.setString(4, flightEntity.getArriveAirport());
            /*data类型：java.sql.data 与 java.util.data
             * 实体类层需要java.util.data，而SQL语句用java.sql.data，
             * 所以需要new出java.sql.data
             * new Date(flightEntity.getDepartureTime().getTime()):
             * 将 java.util.data 转化成 java.sql.data
             * getTime()：将 Data 类型转化成 Long 类型
             * */
            preparedStatement.setDate(5, new Date(flightEntity.getDepartureTime().getTime()));
            preparedStatement.setDate(6, new Date(flightEntity.getArriveTime().getTime()));
            preparedStatement.setString(7, flightEntity.getModel());
            preparedStatement.setFloat(8, flightEntity.getCost());
            preparedStatement.setInt(9, flightEntity.getId());

            int result = preparedStatement.executeUpdate();
            ChenJdbcUtils.commitTransaction(connection);
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            ChenJdbcUtils.rollBackTransaction(connection);
            return 0;
        } finally {
            // 释放资源
            ChenJdbcUtils.closeConnection(preparedStatement, connection);
        }
    }

    /*
     * 添加航班信息
     * */
    public int insertFlight(FlightEntity flightEntity) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 1.获取连接
            connection = ChenJdbcUtils.getConnection();
            // 2.开启事务
            ChenJdbcUtils.beginTransaction(connection);
            //3.获取执行者对象
            preparedStatement = connection.prepareStatement(
                    "INSERT INTO `chen`.`chen_flight` (`id`, `flight_id`," +
                            " `company`, `departure_airport`, `arrive_airport`, " +
                            "`departure_time`, `arrive_time`, `model`, `cost`, `is_delete`)" +
                            " VALUES (null, ?, ?, ?," +
                            " ?, ?, ?, ?, ?, 0);\n");
            preparedStatement.setString(1, flightEntity.getFlightId());
            preparedStatement.setString(2, flightEntity.getCompany());
            preparedStatement.setString(3, flightEntity.getDepartureAirport());
            preparedStatement.setString(4, flightEntity.getArriveAirport());
            /*data类型：java.sql.data 与 java.util.data
             * 实体类层需要java.util.data，而SQL语句用java.sql.data，
             * 所以需要new出java.sql.data
             * new Date(flightEntity.getDepartureTime().getTime()):
             * 将 java.util.data 转化成 java.sql.data
             * getTime()：将 Data 类型转化成 Long 类型
             * */
            preparedStatement.setDate(5, new Date(flightEntity.getDepartureTime().getTime()));
            preparedStatement.setDate(6, new Date(flightEntity.getArriveTime().getTime()));
            preparedStatement.setString(7, flightEntity.getModel());
            preparedStatement.setFloat(8, flightEntity.getCost());

            int result = preparedStatement.executeUpdate();
            ChenJdbcUtils.commitTransaction(connection);
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            ChenJdbcUtils.rollBackTransaction(connection);
            return 0;
        } finally {
            // 释放资源
            ChenJdbcUtils.closeConnection(preparedStatement, connection);
        }
    }

    public List<FlightEntity> searchByAll(String departureAirport, String arriveAirport) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1.获取连接
            connection = ChenJdbcUtils.getConnection();
            //2.获取执行者对象
            preparedStatement = connection.prepareStatement(
                    "select * from chen_flight " +
                    "where is_delete = 0 and " +
                    "departure_airport = ? and " +
                    "arrive_airport = ?");
            preparedStatement.setString(1, departureAirport);
            preparedStatement.setString(2, arriveAirport);
            resultSet = preparedStatement.executeQuery();
            ArrayList<FlightEntity> flightEntities = new ArrayList<>();
            while (resultSet.next()) {
                Integer id = resultSet.getInt("id");
                String flightId = resultSet.getString("flight_id");
                String company = resultSet.getString("company");
                Date departureTime = resultSet.getDate("departure_time");
                Date arriveTime = resultSet.getDate("arrive_time");
                String model = resultSet.getString("model");
                Float cost = resultSet.getFloat("cost");
                Integer isDelete = resultSet.getInt("is_delete");
                FlightEntity flightEntity = new FlightEntity(
                        id, flightId, company, departureAirport,
                        arriveAirport, departureTime,
                        arriveTime, model, cost, isDelete);
                // 存入到集合中
                flightEntities.add(flightEntity);
            }
            return flightEntities;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            // 释放资源
            ChenJdbcUtils.closeConnection(resultSet, preparedStatement, connection);
        }
    }
}
